package com.eling.elcms.travel.dao.hibernate;

import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import com.eling.elcms.travel.dao.IPlanItemGroupDao;
import com.eling.elcms.travel.model.AdjustType;
import com.eling.elcms.travel.model.PlanItemGroup;
import com.eling.elcms.travel.model.view.PlanItemGroupView;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class PlanItemGroupDaoHibernate extends GenericDaoHibernate<PlanItemGroup, Long> implements IPlanItemGroupDao{

	public PlanItemGroupDaoHibernate() {
		super(PlanItemGroup.class);
	}
	
	
	@SuppressWarnings("unchecked")
	@Override
	public List<PlanItemGroupView> querySignUpRefund(PlanItemGroup cond) {
		SimpleDateFormat sdf =   new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT");
		sql.append("	a.pkPlanItemGroup, a.pkServicePoint,a.pkPlanItem,a.startDate,a.endDate,a.pkDestination,a.destinationName, COUNT(1) signUpNum,");
		sql.append("    COUNT(srt.pkSignUpRefund) refundsNum,");
		sql.append("	COUNT(srt.refund) alreadyRefundsNum,");
		sql.append("  (case when find_in_set('Abolished', a.adjusttypes1) then 'Abolished' else 'Turnover' end) adjustTypes");
		sql.append(" FROM");
		sql.append("	(");
		sql.append("		SELECT");
		sql.append("			pg.pkPlanItemGroup,pg.pkServicePoint,pi.pkPlanItem,pi.startDate,pi.endDate,d.pkDestination,d.`name` destinationName,GROUP_CONCAT(ma.adjusttypes) adjusttypes1");
		sql.append("		FROM");
		sql.append("			travel_planitemgroup pg");
		sql.append("		LEFT JOIN travel_mem_adjusttypes ma ON ma.pkMemGroup = pg.pkPlanItemGroup");
		sql.append("		LEFT JOIN travel_planitem pi ON pi.pkPlanItem = pg.pkPlanItem");
		sql.append("		LEFT JOIN travel_destination d ON pi.pkDestination = d.pkDestination");
		sql.append("		WHERE");
		sql.append("			pg.isAdjustment = TRUE");
		sql.append("		AND ma.adjusttypes IN ('Abolished', 'Turnover')");
		if(cond.getServicePoint()!=null){
			sql.append("		AND pg.pkServicePoint = "+cond.getServicePoint().getPkServicePoint());	
		}
		if(cond.getPlanItem() != null && cond.getPlanItem().getDestination() != null ){
			sql.append("		AND d.pkDestination = "+cond.getPlanItem().getDestination().getPkDestination());
		}
		if(cond.getPlanItem()!=null && cond.getPlanItem().getStartDate()!=null){
			if(cond.getPlanItem().getStartDate()!=null){
				sql.append("		AND pi.startDate >= '"+sdf.format(cond.getPlanItem().getStartDate())+"'");
			}
			if(cond.getPlanItem().getEndDate()!=null){
				sql.append("		AND pi.startDate <= '"+sdf.format(cond.getPlanItem().getEndDate())+"'");
			}
 		}
		sql.append("		GROUP BY");
		sql.append("			pg.pkPlanItemGroup");
		sql.append("	) a");
		sql.append(" LEFT JOIN travel_signupregister sr ON a.pkPlanItemGroup = sr.pkPlanItemGroup");
		sql.append(" LEFT JOIN travel_signupcharge sc ON sr.pkSignUpRegister = sc.pkSignUpRegister");
		sql.append(" LEFT JOIN travel_signuprefund srt ON sc.pkSignUpCharge = srt.pkSignUpCharge");
		sql.append(" WHERE");
		sql.append("	srt.pkSignUpRefund IS NOT NULL");
		sql.append(" GROUP BY");
		sql.append("	a.pkPlanItemGroup");
		
		List<Object[]> objList = getSession().createNativeQuery(sql.toString()).list();
		List<PlanItemGroupView> groupList = new ArrayList<PlanItemGroupView>();
		
		for(Object[] obj:objList){
			PlanItemGroupView p = new PlanItemGroupView();
			p.setPkPlanItemGroup(new Long(obj[0].toString()));
			p.setPkServicePoint(new Long(obj[1].toString()));
			p.setPkPlanItem(new Long(obj[2].toString()));
			p.setStartDate((Date)obj[3]);
			p.setEndDate((Date)obj[4]);
			p.setPkDestination(new Long(obj[5].toString()));
			p.setDestinationName(obj[6].toString());	
			//报名人数（去除掉退团人后的报名人数）
			PlanItemGroup pig = new PlanItemGroup();
			pig.setPkPlanItemGroup(new Long(obj[0].toString()));
			pig = query(pig).get(0);
			p.setSignUpNum(pig.getSignUpNum());
			//p.setSignUpNum(pig.getSignUpNum() + new Integer(obj[8].toString()));//退团之前的报名人数数量。
			
			//p.setSignUpNum(new Integer(obj[7].toString()));
			p.setRefundsNum(new Integer(obj[8].toString()));
			p.setAlreadyRefundsNum(new Integer(obj[9].toString()));
			p.setAdjustTypes(AdjustType.valueOf(obj[10].toString()));
			groupList.add(p);
		}
		
		return groupList;
	}


	@SuppressWarnings("unchecked")
	@Override
	public Long queryPlanItemGPk(Long pkGroupReserve) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT   ");
		sql.append("	tg.pkGroupReserve,tg.pkPlanItemGroup   ");
		sql.append("FROM   ");
		sql.append("	travel_groupreserve tg   ");
		sql.append("WHERE   ");
		sql.append("tg.pkGroupReserve="+pkGroupReserve+"   ");
		List<Object[]> list = getSession().createNativeQuery(sql.toString()).list();
		Object[] objects = list.get(0);
		String str = objects[1].toString();
		long l = Long.parseLong(str);
		return l;
	}
	
}
